from sqlalchemy3 import Column, Integer, String, select, func
from sqlalchemy3.orm import Session

from .base import Base, engine


class User(Base):
    __tablename__ = "user"
    id = Column(Integer, primary_key=True, autoincrement=True)  # 用户编号
    username = Column(String(36), unique=True)  # 用户名
    password = Column(String(128), nullable=True)  # 密码


# 添加用户
def add_user(username, password):
    with Session(engine) as session:
        session.add(User(username=username, password=password))
        session.commit()


# 修改用户
def update_user(id, username, password):
    with Session(engine) as session:
        query = select(User).where(User.id == id)
        user = session.execute(query).scalars().first()
        user.username = username
        user.password = password
        session.add(user)
        session.commit()


# 删除用户
def delete_user(id):
    with Session(engine) as session:
        user = session.get(User, id)
        if user:
            session.delete(user)
            session.commit()


# 根据用户名查找用户
def get_user_by_username(username):
    with Session(engine) as session:
        stmt = select(User).where(User.username == username)
        user = session.execute(stmt).scalars().first()
    return user


# 判断用户是否存在
def is_exist_user(
        id=None,
        username=None,
):
    if id:
        with Session(engine) as session:
            user = session.get(User, id)
        return user is not None
    if username:
        return get_user_by_username(username) is not None
    return False


# 分页查询用户
def get_user_page(page: int = 1, size: int = 20):
    with Session(engine) as session:
        # 查询总记录数
        total = session.scalar(select(func.count()).select_from(User))

        # 分页查询用户
        query = select(User.id, User.username).order_by(User.id.desc()).offset((page - 1) * size).limit(size)
        results = session.execute(query).all()

        # 将结果转换为字典列表
        data = [{"id": row.id, "username": row.username} for row in results]

        return {
            "page": page,
            "size": size,
            "total": total,
            "data": data
        }
